CS157a Fall 2018Practice Final

To study for the final I would suggest you: (1) Know how to do (by heart) all the practice problems. (2) Go over your notes at least three times. Second and third time try to see how much you can remember from the first time. (3) Go over the homework problems. (4) Try to create your own problems similar to the ones I have given and solve them. (5) Skim the relevant sections from the book. (6) If you want to study in groups, at this point you are ready to quiz each other. The practice final is below. Here are some facts about the actual final: (a) It is comprehensive (b) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (c) You should bring photo ID. (d) There will be more than one version of the test. Each version will be of comparable difficulty. (e) It is 10 problems (2pts each), 6 problems will be on materials since the second midterm, 2 problems each will be from the topics of each of the midterm. (f) Two problems will be exactly (less typos) off of the practice final, and one will be off of the practice midterm.

1. Express the following in SQL: (a) $(R \bowtie S)$, (b) `(R \cup S) \cap T` as bags
2. Express the following in SQL: (a) The average net worth of movie execs, (b) The name and total number of minutes of film of producers who produced a film with Harrison Ford.
3. Give SQL to do the following DML operations: (a) Create a table `R(A:i\nt, B:i\nt, C:i\nt)` where A,B is the primary key, C is a key, the default value for `B` is 10, and `B` must be at least 5, (b) Insert into R all distinct values (A, B, C) from S(A:int, B:int, C:int, D:int) where D > 10
4. Give SQL for the following operations on relation `R(A:i\nt, B:i\nt, C:i\nt)`: (a) Delete all rows of R where `A > 5` and `B < 10`, (b) Update the salaries of all MovieExecs with a salary less than 10,000,000 to make it 10,000,000.
5. What is a tablespace? Give the SQL command to create a tablespace using the folder /usr/local/my-space.
6. Give the SQL syntax to create a table WorksOn(emp_id:INT, pid:INT) where emp_id has a foreign key constraint referencing the Employee ID attribute and pid has a foreign key constraint referencing Project ID. Explain the Cascade policy and how we could use it to handle DELETE modifications to this table.
7. Explain what a SQL ASSERTION is used for and give an example of SQL statement declaring an assertion.
8. Give the class summary of the SQL standard conditions for when a view is updatable.
9. Give the Mysql SQL to create a hash index named boo on table foo's A and B attributes.
10. Give the Java code needed to initialize the JDBC driver for Mysql and open a connection to the database foo on localhost using the default port for Mysql.